const main = {
  add: `INSERT INTO Staffs
    (userId, jName, city, salary, showResume) VALUES
    (?, ?, ?, ?, ?)`,
  update: `UPDATE Staffs SET
    jName = ?,
    city = ?,
    salary = ?,
    showResume = ?
    WHERE id = ?`,
  select: `SELECT s.*, u.*, s.id AS staffId FROM Staffs s
    LEFT JOIN User u ON u.id = s.userId
    WHERE (? IS NULL OR s.jName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR s.city LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR s.userId = ?)
    ORDER BY s.id DESC
    LIMIT ?, ?`,
  count: `SELECT COUNT(*) FROM Staffs s
    WHERE (? IS NULL OR s.jName LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR s.city LIKE CONCAT('%', ?, '%'))
    AND (? IS NULL OR s.userId = ?)`,
  
  detail: `SELECT s.*, u.*, r.* FROM Staffs s
    LEFT JOIN User u ON u.id = s.userId
    LEFT JOIN Resume r ON r.id = s.userId
    WHERE s.id = ?`,
  
  delete: `DELETE FROM Staffs WHERE id = ?`
};

module.exports = main;